/*
EXEC SP_LOAD_ALL_PART_NUMBERS_BY_PROCESS 'FG', 1, 1,0
*/
ALTER PROC SP_LOAD_ALL_PART_NUMBERS_BY_PROCESS 
(
	@PROCESS_TYPE 			VARCHAR(50), 	-- HERE 0 MEANS ALL PROCESS PRODUCTS OTHERWISE FETCH PRODUCTS BY PROCESS 
	@PRODUCT_TYPE 			INT, 		-- HERE 0 = ALL( DIE+BOARD+KIT ), 1 = DIE , 2 = BOM, 3 = only Board & Kits
	@WITHOUT_OBSOLETE_PRODUCTS 	BIT,		-- HERE 0 MEANS VALID PART NO'S (NOT OBSULETE) ONLY OTHERWISE ALL PART NO'S
	@PARENT_PRODUCT_ID 		INT		-- HERE 0 MEANS ALL BASE PRODUCTS OTHERWISE FETCH ONLY GIVEN BASE PRODUCT PART NUMBERS
)
AS
BEGIN
	DECLARE @SQLSTRING NVARCHAR(4000)

	IF @PROCESS_TYPE = 'TAPE'
	BEGIN
		SET @PROCESS_TYPE = 'TAPE & REEL'
	END

	SET @SQLSTRING = N'SELECT DISTINCT PS_TYPE, PS_TYPE_NAME, PART_NUMBER, P.PROCESSSTEP_ID, PRODUCT_ID '
	SET @SQLSTRING = @SQLSTRING + ', (CASE
			WHEN PS_TYPE = ''FAB'' THEN 1
			WHEN PS_TYPE = ''FAB OPTION'' THEN 2
			WHEN PS_TYPE = ''WAFER BANK'' THEN 3
			WHEN PS_TYPE = ''Die Bank'' THEN 4
			WHEN PS_TYPE = ''ASSEMBLY'' THEN  5
			WHEN PS_TYPE = ''FPO'' THEN 6
			WHEN PS_TYPE = ''FT''  THEN  7
			WHEN PS_TYPE = ''TAPE & REEL'' THEN 8
			WHEN PS_TYPE = ''FG'' THEN 9
			ELSE 10
		   	END ) AS PS_TYPE_ID, (CASE WHEN P_TYPE IS NULL THEN 1 WHEN P_TYPE = ''BOARD'' THEN 3 WHEN P_TYPE = ''KIT'' THEN 5 END) AS P_TYPE_ORDER '
	SET @SQLSTRING = @SQLSTRING + '	 FROM PRODUCT P (NOLOCK), PROCESS_STEP PS (NOLOCK)  WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID'

	IF( @WITHOUT_OBSOLETE_PRODUCTS = 0 )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND Part_Status <> ''Obsolete'''
	END

	IF( @PRODUCT_TYPE = 0 )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ( P_TYPE IS NULL OR P_TYPE = ''BOARD'' OR P_TYPE = ''KIT'' ) '
	END
	ELSE IF( @PRODUCT_TYPE = 1 )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P_TYPE IS NULL '
	END
	ELSE IF( @PRODUCT_TYPE = 2 )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P_TYPE = ''BOM'''
	END
	ELSE IF( @PRODUCT_TYPE = 3 )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ( P_TYPE = ''BOARD'' OR P_TYPE = ''KIT'' ) '
	END

	IF( @PARENT_PRODUCT_ID > 0 )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P_PARENT_PRODUCT_ID = '+ CONVERT( VARCHAR, @PARENT_PRODUCT_ID )
	END	
	
	IF( @PROCESS_TYPE <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND  PS_TYPE = ''' + @PROCESS_TYPE + ''''
	END
	
	IF ( (@PROCESS_TYPE = 'FG' OR @PROCESS_TYPE = '0') AND @PRODUCT_TYPE = 0 )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' UNION SELECT ''FG'', ''Finished Goods'', ''---- BOARD -----'', -1, -1, 9, 2'
		SET @SQLSTRING = @SQLSTRING + ' UNION SELECT ''FG'', ''Finished Goods'', ''---- KIT -----'', -1, -1, 9, 4'
	END
	
	IF( @PROCESS_TYPE = '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' ORDER BY P_TYPE_ORDER,PS_TYPE_ID, PART_NUMBER'
	END

	IF( @PROCESS_TYPE <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' ORDER BY P_TYPE_ORDER,PART_NUMBER'
	END	
	
	--PRINT @SQLSTRING
	EXEC ( @SQLSTRING )
END

